/**
 * @project_name staff_vehicle
 * @package_name com.ssh.dao
 * @desc TODO
 * @param
 * @author jack
 * @date 2013-4-24
 * @time 下午12:16:16
 */
package com.ssh.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.transform.AliasToBeanConstructorResultTransformer;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.jdbc.object.SqlQuery;

import com.ssh.entity.StaffApplyCar;
import com.ssh.entity.StaffUseCar;
import com.ssh.model.ACarResult;
import com.ssh.model.pdata.StaffVehicleIOCPData;

/**
 * @author jack
 *
 */
public class StaffUseCarDAOImpl implements StaffUseCarDAOI {
	private SessionFactory sessionFactory;

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#addStaffUC(com.ssh.entity.StaffUseCar)
	 */
	@Override
	public StaffUseCar addStaffUC(StaffUseCar staffUseCar) {
		// TODO Auto-generated method stub
		try {
			sessionFactory.getCurrentSession().save(staffUseCar);
		} catch (Exception e) {
			return null;
		}
		return staffUseCar;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#addStaffUCList(java.util.List)
	 */
	@Override
	public int addStaffUCList(List<StaffUseCar> staffUCList) {
		// TODO Auto-generated method stub
		int length = staffUCList.size();
		try {
			for (int i = 0; i < length; i++) {
				addStaffUC(staffUCList.get(i));
			}
		} catch (Exception e) {
			return 0;
		}
		return length;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#countById(java.lang.String)
	 */
	@Override
	public int countById(String id) {
		// TODO Auto-generated method stub
		return 0;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#deleteById(java.lang.String)
	 */
	@Override
	public int deleteById(String id) {
		// TODO Auto-generated method stub
		return 0;
	}

	/**
	 * @return the sessionFactory
	 */
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#queryByIdList(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<StaffUseCar> queryByIdList(String strings) {
		// TODO Auto-generated method stub
		List<StaffUseCar> staffUCList;
		try {
			String hql = "from StaffUseCar as staffUseCar where staffUseCar.staffId=?";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString(0, strings);
			staffUCList = query.list();
		} catch (Exception e) {
			return null;
		}
		return staffUCList;
	}

	/**
	 * 通过状态查询出员工可用的车辆信息
	 */
	@Override
	public StaffUseCar queryBySIdState(String staffId,String state) {
		// TODO Auto-generated method stub
		StaffUseCar staffUseCar;
		try {
			String hql = "from StaffUseCar as staffUseCar "
					+ "where staffUseCar.usecarStaffid=:usecarStaffid and staffUseCar.state=:state";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setParameter("usecarStaffid", staffId);
			query.setParameter("state", state);
			staffUseCar = (StaffUseCar) query.uniqueResult();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return staffUseCar;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#queryMyApplyCarInfo(java.lang.String)
	 */
	@Override
	public List<ACarResult> queryMyApplyCarInfo(String staffId) {
		// TODO Auto-generated method stub
		String sql = "select a.staff_id as staffId ,"
				+ "a.apply_date as applyDate,a.reviewer_id as reviewerId,"
				+ "a.r_comments as RComments, u.driver_id as driverId,"
				+ "u.vehicle_id as vehicleId,i.type as type "
				+ "FROM staff_apply_car AS a ,staff_use_car as u ,inner_vehicle AS i "
				+ "WHERE a.staff_id=u.staff_id AND u.vehicle_id=i.vehicle_id";
		SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
		query.addScalar("staffId", StandardBasicTypes.STRING);
		query.addScalar("applyDate", StandardBasicTypes.STRING);
		query.addScalar("reviewerId", StandardBasicTypes.STRING);
		query.addScalar("RComments", StandardBasicTypes.STRING);
		query.addScalar("driverId", StandardBasicTypes.STRING);
		query.addScalar("vehicleId", StandardBasicTypes.STRING);
		query.addScalar("type", StandardBasicTypes.STRING);
		query.setResultTransformer(new AliasToBeanResultTransformer(
				ACarResult.class));
		@SuppressWarnings("unchecked")
		List<ACarResult> aCarResult = query.list();
		return aCarResult;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#querySUseCar(java.lang.String,
	 * java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<StaffVehicleIOCPData> querySUseCar(String staffId,
			String staffUseId, String driverId, String sucState) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * @param sessionFactory
	 *            the sessionFactory to set
	 */
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarIn(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
   /**	通过使用人 申请人 驾驶员ID查询出相关信息*/
	@Override
	public StaffVehicleIOCPData staffUseCarIn(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		String hql = "select new StaffVehicleIOCPData() from Staff s,staffUseCar su,innerVehicle i"
				+ "where s.staffId=su.staffId and s.vehicleId and i.vehicleId=su.vehicleId and su.driverId=:driverId"
				+ "and su.usecarStaffid =:staffUseId and su.staffId=:staffId and su.state=:state";
		sessionFactory.getCurrentSession().createQuery(hql).uniqueResult();
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarInUpdate(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public StaffVehicleIOCPData staffUseCarInUpdate(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarOut(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public StaffVehicleIOCPData staffUseCarOut(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		String hql = "select new StaffVehicleIOCPData() from Staff s,staffUseCar su,innerVehicle i"
				+ "where s.staffId=su.staffId and s.vehicleId and i.vehicleId=su.vehicleId and su.driverId=:driverId"
				+ "and su.usecarStaffid =:staffUseId and su.staffId=:staffId and su.state=:state";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		return (StaffVehicleIOCPData) query
				.setParameter("staffUseId", staffUseId)
				.setParameter("staffId", staffId).setParameter("state", "in")
				.uniqueResult();
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarOutQuery(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public StaffVehicleIOCPData staffUseCarOutQuery(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarOutUpdate(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public StaffVehicleIOCPData staffUseCarOutUpdate(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#staffUseCarQuery(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public StaffVehicleIOCPData staffUseCarQuery(String staffId,
			String staffUseId, String driverId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffUseCarDAOI#updateBySUC(com.ssh.entity.StaffUseCar)
	 */
	@Override
	public StaffUseCar updateBySUC(StaffUseCar staffUseCar) {
		// TODO Auto-generated method stub
		try {
			sessionFactory.getCurrentSession().update(staffUseCar);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return staffUseCar;
	}

	@Override
	public StaffVehicleIOCPData getAllstaffUseCarInf(String staffId,
			String staffUseId, String driverId, String state) {
		// TODO Auto-generated method stub
		String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sfu,su,iv,id) " +
				" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
				"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
				"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseId and su.staffId=:staffId" +
				" and su.driverId=:driverId and su.state=:state";
		Query query = sessionFactory.getCurrentSession().createQuery(hql).
				setParameter("staffId", staffId).setParameter("staffUseId", staffUseId).setParameter("driverId", driverId)
				.setParameter("state", state);
		return (StaffVehicleIOCPData) query.uniqueResult();
	}

	@Override
	public StaffVehicleIOCPData getAllstaffUseCarInf(String staffId,
			String staffUseId, String state) {
		// TODO Auto-generated method stub
		String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sfu,su,iv,id) " +
				" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
				"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
				"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseId and su.staffId=:staffId and su.state=:state";
		Query query = sessionFactory.getCurrentSession().createQuery(hql).
				setParameter("staffId", staffId).setParameter("staffUseId", staffUseId).setParameter("state", state);
		return (StaffVehicleIOCPData) query.uniqueResult();
	}

	@Override
	public StaffVehicleIOCPData getAllstaffUseCarInf(String staffUseCarId,
			String state) {
		// TODO Auto-generated method stub
		try{
			String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sd,iv,sfu,su) " +
					" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
					"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
					"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseCarId and su.state=:state" +
					" and id.staffId=sd.staffId";
			Query query = sessionFactory.getCurrentSession().createQuery(hql).
					setParameter("staffUseCarId", staffUseCarId).setParameter("state", state);
			@SuppressWarnings("unchecked")
			List<StaffVehicleIOCPData> staffVehicleIOCPData = query.list();
			if(staffVehicleIOCPData.size()!=0)
				return staffVehicleIOCPData.get(0);
			else
				return null;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
    /**通过使用人ID获得用车相关信息 注 返回结果可能不唯一*/
	@Override
	public StaffVehicleIOCPData getAllstaffUseCarInf(String staffUseId) {
		// TODO Auto-generated method stub
		String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sfu,su,iv,id) " +
				" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
				"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
				"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseId ";
		Query query = sessionFactory.getCurrentSession().createQuery(hql).setParameter("staffUseId", staffUseId)
				.setParameter("state", "use");
		return (StaffVehicleIOCPData) query.uniqueResult();
	}

	@Override
	public StaffVehicleIOCPData getAllsucInfByscs(String staffUseCarId,
			String vehicleId, String state) {
		// TODO Auto-generated method stub
		String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sfu,su,iv,id) " +
				" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
				"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
				"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseCarId and su.state=:state " +
				" and su.vehicleId=:vehicleId ";
		Query query = sessionFactory.getCurrentSession().createQuery(hql).
				setParameter("staffUseCarId", staffUseCarId).setParameter("state", state)
				.setParameter("vehicleId", vehicleId);
		return (StaffVehicleIOCPData) query.uniqueResult();
	}

	@Override
	public StaffVehicleIOCPData getAllsucInfByscs(String staffId,String staffUseCarId,
			String vehicleId, String state) {
		// TODO Auto-generated method stub
		String hql="select new com.ssh.model.pdata.StaffVehicleIOCPData(sfu,su,iv,id) " +
				" from InnerDriver id,InnerVehicle iv,Staff sd,Staff sa,Staff sfu,StaffUseCar su " +
				"where id.staffId=su.driverId and sa.staffId=su.staffId and sfu.staffId=su.usecarStaffid " +
				"and su.vehicleId=iv.vehicleId and su.usecarStaffid=:staffUseCarId and su.state=:state " +
				" and su.vehicleId=:vehicleId and su.staffId=:staffId";
		Query query = sessionFactory.getCurrentSession().createQuery(hql).
				setParameter("staffUseCarId", staffUseCarId).setParameter("state", state)
				.setParameter("vehicleId", vehicleId).setParameter("staffId", staffId);
		return (StaffVehicleIOCPData) query.uniqueResult();
	}

	@Override
	public StaffUseCar queryBySIdState(String staffId) {
		// TODO Auto-generated method stub
		StaffUseCar staffUseCar;
		try {
			String hql = "from StaffUseCar as staffUseCar "
					+ "where staffUseCar.usecarStaffid=:usecarStaffid and staffUseCar.state=:state";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setParameter("usecarStaffid", staffId);
			query.setParameter("state", "confirm");
			staffUseCar = (StaffUseCar) query.uniqueResult();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return staffUseCar;
	}

}
